﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class WaveDetailDA : IWaveDetailDA
    {
        private string CREATED_BY = "@CREATED_BY";
        private string ORDER_ID = "@ORDER_ID";
        private string ORDER_LINE_ID = "@ORDER_LINE_ID";
        private string PROCESS_STATUS = "@PROCESS_STATUS";
        private string SQL_CHECK_WAVE_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@WAVE_DETAIL WHERE WAVE_ID = @WAVE_ID AND ORDER_ID = @ORDER_ID ";
        private string SQL_DELETE_WAVE_DETAIL = " DELETE FROM @_@WAVE_DETAIL WHERE WAVE_ID = @WAVE_ID AND ORDER_ID = @ORDER_ID ";
        private string SQL_INSERT_WAVE_DETAIL = " INSERT INTO @_@WAVE_DETAIL ( WH_ID, WAVE_ID, ORDER_ID,ORDER_LINE_ID, PROCESS_STATUS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @WAVE_ID, @ORDER_ID,@ORDER_LINE_ID, @PROCESS_STATUS, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_SELECT_ALL_WAVE_DETAIL = " SELECT  ROWID, WH_ID, WAVE_ID, ORDER_ID,ORDER_LINE_ID, PROCESS_STATUS, TS, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE  FROM  @_@WAVE_DETAIL ";
        private string SQL_SELECT_ORDERS_BY_WAVE_ID = " SELECT O.ORDER_ID,O.EXTERN_ORDER_ID,O.OWNER_ID,O.REFERENCE_NUM,OD. SKU_GROUP_COUNT,OD.TOTALE_QTY,O.STATUS,T.[TEXT] AS STATUS_D FROM @_@WAVE_DETAIL WD LEFT JOIN  @_@ORDERS O ON WD.ORDER_ID = O.ORDER_ID LEFT JOIN (SELECT ORDER_ID,SUM(ISNULL(PICKED_QTY,0)) AS TOTALE_QTY, COUNT(*) AS SKU_GROUP_COUNT FROM @_@ORDER_DETAIL GROUP BY ORDER_ID ) AS OD ON OD.ORDER_ID =O.ORDER_ID  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = O.STATUS AND SCD.SYS_CODE_TYPE = 'OrderStatus'  LEFT JOIN sysadm.TRANSLATION T ON 'OrderStatus.'+O.STATUS = T.ITEM_ID   AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE INNER JOIN SYSADM.USER_OWNER UO ON O.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_UPADTE_WAVE_DETAIL_STATUS_BY_ORDER_ID = " UPDATE WAVE_DETAIL  SET PROCESS_STATUS = @PROCESS_STATUS,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE()  FROM @_@WAVE_DETAIL WAVE_DETAIL  LEFT JOIN @_@ORDERS ORDERS ON WAVE_DETAIL.ORDER_ID = ORDERS.ORDER_ID  WHERE WAVE_DETAIL.ORDER_ID = @ORDER_ID AND ORDERS.BATCH_FLAG = 'Y' ";
        private string SQL_UPADTE_WAVE_DETAIL_STATUS_BY_ORDER_ID_ORDER_LINE_ID = " UPDATE  @_@WAVE_DETAIL  SET PROCESS_STATUS = @PROCESS_STATUS,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE()  WHERE ORDER_ID = @ORDER_ID AND  ORDER_LINE_ID=@ORDER_LINE_ID ";
        private string SQL_UPADTE_WAVE_DETAIL_STATUS_BY_ORDER_ID_ORDER_LINE_ID_BATCH_FLAG_IS_Y = " UPDATE WAVE_DETAIL  SET PROCESS_STATUS = @PROCESS_STATUS,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE()  FROM @_@WAVE_DETAIL WAVE_DETAIL  LEFT JOIN @_@ORDER_DETAIL OD ON WAVE_DETAIL.ORDER_ID = OD.ORDER_ID AND WAVE_DETAIL.ORDER_LINE_ID= OD.LINE_ID  WHERE WAVE_DETAIL.ORDER_ID = @ORDER_ID AND ORDER_LINE_ID=@ORDER_LINE_ID AND OD.BATCH_FLAG = 'Y' ";
        private string SQL_UPADTE_WAVE_DETAIL_STATUS_BY_WAVE_ID = " UPDATE @_@WAVE_DETAIL SET PROCESS_STATUS = @PROCESS_STATUS,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE WAVE_ID=@WAVE_ID ";
        private string SQL_UPDATE_WAVE_DETAIL = " UPDATE @_@WAVE_DETAIL SET WH_ID = @WH_ID, PROCESS_STATUS = @PROCESS_STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE WAVE_ID = @WAVE_ID AND ORDER_ID = @ORDER_ID ";
        private string TS = "@TS";
        private string UPDATED_BY = "@UPDATED_BY";
        private string WAVE_ID = "@WAVE_ID";
        private string WH_ID = "@WH_ID";

        public bool CheckWaveDetailIDUnique(string waveID, string orderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID), new SqlParameter("@ORDER_ID", orderID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_WAVE_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckWaveDetailIDUnique(DataBase dataBase, DbTransaction tran, string waveID, string orderID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID), new SqlParameter("@ORDER_ID", orderID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_WAVE_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public int DeleteWaveDetail(List<string> waveID, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            string strSql = " DELETE FROM @_@WAVE_DETAIL WHERE WAVE_ID = @WAVE_ID ";
            try
            {
                for (int j = 0; j < waveID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID[j]) };
                    result += db.ExecuteNonQuery(tran, CommandType.Text, strSql.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteWaveDetail(string waveID, List<string> orderID, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                for (int j = 0; j < orderID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID), new SqlParameter("@ORDER_ID", orderID[j]) };
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_WAVE_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteWaveDetail(DataBase dataBase, DbTransaction tran, List<string> waveID, string whLoginID)
        {
            string strSql = " DELETE FROM @_@WAVE_DETAIL WHERE WAVE_ID = @WAVE_ID ";
            int result = 0;
            try
            {
                for (int j = 0; j < waveID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteWaveDetail(DataBase dataBase, DbTransaction tran, string waveID, List<string> orderID, string whLoginID)
        {
            int result = 0;
            try
            {
                for (int j = 0; j < orderID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID), new SqlParameter("@ORDER_ID", orderID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_DELETE_WAVE_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllWaveDetail(string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_WAVE_DETAIL.Replace("@_@", whLoginID);
            return DBHelper.ExecuteDataSet(CommandType.Text, sql);
        }

        public DataSet GetAllWaveDetail(DataBase dataBase, DbTransaction tran, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_WAVE_DETAIL.Replace("@_@", whLoginID);
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql);
        }

        public List<OrdersInfo> GetOrdersInfoByWaveID(string waveID, string whLoginID, PubParasInfo puParasInfo)
        {
            string sql = this.SQL_SELECT_ORDERS_BY_WAVE_ID.Replace("@_@", whLoginID) + " WHERE WD.WAVE_ID = @WAVE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID), new SqlParameter("@LANGUAGE", puParasInfo.UserLanguage), new SqlParameter("@USER_ID", puParasInfo.UserID) };
            List<OrdersInfo> ordersList = new List<OrdersInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    OrdersInfo ordersInfo = new OrdersInfo(reader["ORDER_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["STATUS"].ToString(), reader["STATUS_D"].ToString(), reader["EXTERN_ORDER_ID"].ToString(), reader["REFERENCE_NUM"].ToString(), (reader["TOTALE_QTY"].ToString() != "") ? decimal.Parse(reader["TOTALE_QTY"].ToString()) : 0M, (reader["SKU_GROUP_COUNT"].ToString() != "") ? int.Parse(reader["SKU_GROUP_COUNT"].ToString()) : 0);
                    ordersList.Add(ordersInfo);
                }
            }
            return ordersList;
        }

        public WaveDetailInfo GetWaveDetailByID(string waveID, string orderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_WAVE_DETAIL.Replace("@_@", whLoginID) + " WHERE WAVE_ID = @WAVE_ID  AND ORDER_ID = @ORDER_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID), new SqlParameter("@ORDER_ID", orderID) };
            WaveDetailInfo waveDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    waveDetailInfo = new WaveDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["WAVE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PROCESS_STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return waveDetailInfo;
        }

        public WaveDetailInfo GetWaveDetailByID(DataBase dataBase, DbTransaction tran, string waveID, string orderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_WAVE_DETAIL.Replace("@_@", whLoginID) + " WHERE WAVE_ID = @WAVE_ID AND ORDER_ID = @ORDER_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID), new SqlParameter("@ORDER_ID", orderID) };
            WaveDetailInfo waveDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                waveDetailInfo = new WaveDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["WAVE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PROCESS_STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return waveDetailInfo;
        }

        public WaveDetailInfo GetWaveDetailByOrderID(string orderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_WAVE_DETAIL.Replace("@_@", whLoginID) + " WHERE ORDER_ID = @ORDER_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            WaveDetailInfo waveDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    waveDetailInfo = new WaveDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["WAVE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PROCESS_STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
                }
            }
            return waveDetailInfo;
        }

        public WaveDetailInfo GetWaveDetailByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_WAVE_DETAIL.Replace("@_@", whLoginID) + " WHERE ORDER_ID = @ORDER_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID) };
            WaveDetailInfo waveDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                waveDetailInfo = new WaveDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["WAVE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PROCESS_STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : new DateTime(), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : new DateTime());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return waveDetailInfo;
        }

        public DataSet GetWaveDetailByQueryList(List<string> sqlWhere, WaveDetailQueryEntity wavedetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_WAVE_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!wavedetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, wavedetailQuery.CurrentPage, wavedetailQuery.PageSize, wavedetailQuery.SortField, wavedetailQuery.SortDirection);
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetWaveDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, WaveDetailQueryEntity wavedetailQuery, string whLoginID)
        {
            string temp = this.SQL_SELECT_ALL_WAVE_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!wavedetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, wavedetailQuery.CurrentPage, wavedetailQuery.PageSize, wavedetailQuery.SortField, wavedetailQuery.SortDirection);
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public List<WaveDetailInfo> GetWaveDetailListByID(string waveID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_WAVE_DETAIL.Replace("@_@", whLoginID) + " WHERE WAVE_ID = @WAVE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID) };
            List<WaveDetailInfo> waveDetailList = new List<WaveDetailInfo>();
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    WaveDetailInfo waveDetailInfo = new WaveDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["WAVE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PROCESS_STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                    waveDetailList.Add(waveDetailInfo);
                }
            }
            return waveDetailList;
        }

        public List<WaveDetailInfo> GetWaveDetailListByID(DataBase dataBase, DbTransaction tran, string waveID, string whLoginID)
        {
            string sql = this.SQL_SELECT_ALL_WAVE_DETAIL.Replace("@_@", whLoginID) + " WHERE WAVE_ID = @WAVE_ID";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID) };
            List<WaveDetailInfo> waveDetailList = new List<WaveDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                WaveDetailInfo waveDetailInfo = new WaveDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["WAVE_ID"].ToString(), reader["ORDER_ID"].ToString(), (reader["ORDER_LINE_ID"].ToString() != "") ? int.Parse(reader["ORDER_LINE_ID"].ToString()) : 0, reader["PROCESS_STATUS"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()));
                waveDetailList.Add(waveDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return waveDetailList;
        }

        public int InsertWaveDetail(WaveDetailInfo wavedetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                wavedetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_WaveDetail_Parameters(wavedetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_WAVE_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertWaveDetail(DataBase dataBase, DbTransaction tran, WaveDetailInfo wavedetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                wavedetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_WaveDetail_Parameters(wavedetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_WAVE_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_WaveDetail_Parameters(WaveDetailInfo waveDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.WAVE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.ORDER_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.PROCESS_STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20) };
            if (!string.IsNullOrEmpty(waveDetailInfo.WhID))
            {
                paramArray[0].Value = waveDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = waveDetailInfo.WaveID;
            paramArray[2].Value = waveDetailInfo.OrderID;
            paramArray[3].Value = waveDetailInfo.OrderLineID;
            paramArray[4].Value = waveDetailInfo.ProcessStatus;
            paramArray[5].Value = waveDetailInfo.CreatedBy;
            paramArray[6].Value = waveDetailInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdateWaveDetail(WaveDetailInfo wavedetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                wavedetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_WaveDetail_Parameters(wavedetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_WAVE_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateWaveDetail(DataBase dataBase, DbTransaction tran, WaveDetailInfo wavedetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                wavedetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_WaveDetail_Parameters(wavedetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPDATE_WAVE_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateWaveDetailStatusByOrderID(string orderID, string status, string updatedBy, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@PROCESS_STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
            return DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_UPADTE_WAVE_DETAIL_STATUS_BY_ORDER_ID.Replace("@_@", whLoginID), paras);
        }

        public int UpdateWaveDetailStatusByOrderID(string orderID, string lineID, string status, string updatedBy, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", lineID), new SqlParameter("@PROCESS_STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
            return DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_UPADTE_WAVE_DETAIL_STATUS_BY_ORDER_ID_ORDER_LINE_ID.Replace("@_@", whLoginID), paras);
        }

        public int UpdateWaveDetailStatusByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string status, string updatedBy, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@PROCESS_STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
            return dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPADTE_WAVE_DETAIL_STATUS_BY_ORDER_ID.Replace("@_@", whLoginID), paras);
        }

        public int UpdateWaveDetailStatusByOrderID(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string status, string updatedBy, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", lineID), new SqlParameter("@PROCESS_STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
            return dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPADTE_WAVE_DETAIL_STATUS_BY_ORDER_ID_ORDER_LINE_ID.Replace("@_@", whLoginID), paras);
        }

        public int UpdateWaveDetailStatusByOrderIDAndBatchFlagIsY(string orderID, string lineID, string status, string updatedBy, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", lineID), new SqlParameter("@PROCESS_STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
            return DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_UPADTE_WAVE_DETAIL_STATUS_BY_ORDER_ID_ORDER_LINE_ID_BATCH_FLAG_IS_Y.Replace("@_@", whLoginID), paras);
        }

        public int UpdateWaveDetailStatusByOrderIDAndBatchFlagIsY(DataBase dataBase, DbTransaction tran, string orderID, string lineID, string status, string updatedBy, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@ORDER_ID", orderID), new SqlParameter("@ORDER_LINE_ID", lineID), new SqlParameter("@PROCESS_STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
            return dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPADTE_WAVE_DETAIL_STATUS_BY_ORDER_ID_ORDER_LINE_ID_BATCH_FLAG_IS_Y.Replace("@_@", whLoginID), paras);
        }

        public int UpdateWaveDetailStatusByWaveID(string waveID, string status, string updatedBy, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID), new SqlParameter("@PROCESS_STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
            return DBHelper.ExecuteNonQuery(CommandType.Text, this.SQL_UPADTE_WAVE_DETAIL_STATUS_BY_WAVE_ID.Replace("@_@", whLoginID), paras);
        }

        public int UpdateWaveDetailStatusByWaveID(DataBase dataBase, DbTransaction tran, string waveID, string status, string updatedBy, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@WAVE_ID", waveID), new SqlParameter("@PROCESS_STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy) };
            return dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_UPADTE_WAVE_DETAIL_STATUS_BY_WAVE_ID.Replace("@_@", whLoginID), paras);
        }
    }
}

